home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Software Vault: The Gold Collection
/
Software Vault - The Gold Collection (American Databankers) (1993).ISO
/
cdr11
/
pdox693.zip
/
TI527.ASC
< prev
next >
Wrap
Text File
|
1992-08-12
|
27KB
|
793 lines
PRODUCT : Paradox NUMBER : 527
VERSION : 3.0
OS : DOS
DATE : August 12, 1992 PAGE : 1/12
TITLE : Performance Notes for Paradox 3.0 Reviewers
Paradox provides an environment in which even a relatively
inexperienced database user can work quickly and productively.
Part of its power comes from the fact that it hides unnecessary
detail from the user. As Paradox is executing an interactive
command from the user, it may also be building tables that allow
major changes to be undone, updating the display or environment
as appropriate, managing memory, checking the integrity of
tables, or ensuring that consistency is maintained between a
table and its forms and reports. The extra services Paradox
performs as a part of many operations, allow the user to
concentrate more on the work to be done than on the steps needed
to get there. This makes Paradox easier to learn and use, but no
less powerful; in fact, Paradox's unobtrusive assistance makes it
more powerful for many users than competitive products.
Consider a menu selection that makes a given record number the
current record. Even for such a simple operation, Paradox does
more than it is asked to do; It also refreshes the display to
show the new current record (in context in its table, if the user
is in "table view"). This is a task that might require a second
command in another database product. The effect of anticipating
the user's likely next request and performing it automatically is
to maintain the continuity of the session, allowing the user to
accomplish more work with less effort.
When reviewing Paradox 3.0's performance, it is important to take
into account that a Paradox command may do more work and deliver
more benefits than are obvious at first. In this paper, we
provide a narrative on the many ways that Paradox provides extra
support behind the scenes, along with tips on specific ways to
measure performance without inadvertently including the extra
support as well. We have tried to organize the discussion by
functional areas, but some topics cut across several areas. In
particular, benchmark results for almost every operation are
affected by Paradox's virtual memory management, so we discuss
this topic first.
Memory Management
Paradox's virtual memory manager makes good use of as much memory
as is available, both to hold recently-used blocks of data in
memory and to store PAL procedures. Consequently, timing results
PRODUCT : Paradox NUMBER : 527
VERSION : 3.0
OS : DOS
DATE : August 12, 1992 PAGE : 2/12
TITLE : Performance Notes for Paradox 3.0 Reviewers
are sensitive to the amount of memory available, relative to the
size of the tables used. When tables are fairly small and there
is ample memory, many operations will find most or all of the
data they need already in memory, and will run quickly. Paradox
2.0 can also use expanded memory -- either LIM 3.2 EMS or EEMS --
and Paradox 3.0 adds support for LIM 4.0 EMS.
One of the benefits of Paradox's memory management is that the
second and later accesses to a record are usually much faster
than the first. This is an advantage in everyday use that may
not show up in benchmark tests, which often start from a clean
state or do sequential operations on large files. In general
use, many operations do access the same fairly small group of
records that previous operations have loaded into memory.
Paradox can be expected to perform well in these cases.
Especially in Paradox 386 and Paradox OS/2, you should tune both
the amount of memory taken and its distribution between storage
of recently-used blocks of data, and storage of PAL procedures
according to your application's needs. Because these products
offer address spaces far larger than DOS's one megabyte, the
amount of memory available to Paradox at start-up can vary
widely. This gives you more leeway: if you are running a very
complex application, you should favor procedure storage space; if
you are managing very large tables, you should favor space for
data blocks. Note that Paradox OS/2 cannot tell how much
physical memory is available, so it takes relatively little
memory by default, on the assumption that you will be running
several other sessions as well. Knowing how much memory is
available and how many sessions you are likely to run, you can
apportion memory for better performance.
Memory Management Benchmarking Tips
* Virtual memory
Remember that tests that start with memory clear do not
let Paradox's memory management work to best advantage.
Some tests should reflect the fact that in everyday work,
it is common to repeatedly use the same small set of data.
PRODUCT : Paradox NUMBER : 527
VERSION : 3.0
OS : DOS
DATE : August 12, 1992 PAGE : 3/12
TITLE : Performance Notes for Paradox 3.0 Reviewers
* EMS
Use expanded memory, preferably expanded memory following
the LIM 4.0 or AST EEMS spec., if possible.
* Paradox 386 and Paradox OS/2
If using Paradox 386 or Paradox OS/2, match your command
line options to your environment and to the kind of work
you will be doing in Paradox.
* Queries
Paradox's Query-by-Example facility provides many examples
of extra services that are provided without explicit
request from the user. Probably the most fundamental is
its handling of duplicate records. Queries often yield
duplicates, even when based on keyed tables, and usually
you do not want duplicates in the answer. Paradox was
designed with the idea that query results, in addition to
being correct, should be presented in an easy and usable
format. Accordingly, Paradox suppresses duplicate records
in the ANSWER table by default and, in the course of doing
so, provides another service: it sorts the ANSWER table.
(You can use CheckPlus instead of Check in the query
statement to omit the sort and retain duplicates, if
desired.) In Paradox 3.0, you can select the sort fields
and choose ascending or descending sort for each, making a
subsequent sort step unnecessary.
FIND queries also provide more support for the user than
is evident initially. When a FIND query is performed, the
only outward consequence is that the cursor is positioned
to the first record that satisfies the query. However,
Paradox also builds an ANSWER table containing all records
that match the selection criteria, making it easy to
locate the desired record even if it is not the first
match. If you know you do not want to see any record but
the first match, it is faster to use Image/Zoom/Record
instead (and note that here too Paradox positions to the
desired record, so you can see it in context).
One final example: Whenever a query changes a table,
Paradox keeps a log of the affected records in the
PRODUCT : Paradox NUMBER : 527
VERSION : 3.0
OS : DOS
DATE : August 12, 1992 PAGE : 4/12
TITLE : Performance Notes for Paradox 3.0 Reviewers
CHANGED, INSERTED, or DELETED table (depending on the type
of query). In the event of a mistake, the entire
operation can quickly be undone using this log table. As
a result, it is safe to make complex changes to a table
without writing procedural code and without exhaustive
testing -- if you make the wrong changes, you can always
reverse them and redo the operation correctly. Overall
productivity is improved as a result.
Query Benchmarking Tips
* Duplicates
If a query will not select duplicates -- for instance, if
the table's key field(s) are selected -- use CheckPlus to
prevent sorting.
* Finding just one record
Use Image/Zoom/Record or the PAL LOCATE command to find a
single record in a table.
* Controlling the ANSWER table's layout
Use query capabilities such as column rotation,
CheckDescending, and the AS operator, to give the ANSWER
table the desired layout. This can eliminate a subsequent
sort or restructure step.
* Indexes
Although Paradox queries always give the same results
whether the queried tables are indexed or not, they
sometimes run much faster when appropriate indexes do
exist. We turn to this topic next.
Paradox offers a variety of indexing alternatives. First,
you may chose to define a primary key for a table (in
which case it is sorted and no two records may have the
same key values) or you may leave the table unkeyed (in
which case it is not ordered and duplicate records are
allowed). Second, you may create a secondary index for
any field in a table, whether it has a primary key or not.
PRODUCT : Paradox NUMBER : 527
VERSION : 3.0
OS : DOS
DATE : August 12, 1992 PAGE : 5/12
TITLE : Performance Notes for Paradox 3.0 Reviewers
Finally, you may chose to rebuild a table's secondary
indexes whenever the table is changed (for indexed tables
only) or only when the table is queried. No matter what
kind of data a table contains or how it is queried and
updated, one of these alternatives will be suitable.
A table that will be queried should generally have a
primary index, since this will speed up searches on key
values. Non-key fields that are often the basis of
selection criteria will benefit from having secondary
indexes. You will notice that it is not even necessary to
tell Paradox which fields to create secondary indexes for;
just ask for QuerySpeedup on the most common queries, and
Paradox will decide where indexes are most needed. This
is a prime example of Paradox's ability to increase rather
than limit users' choices by hiding detail. For most
applications, it is better to request maintained secondary
indexes (these are selected in the Custom Configuration
Program, or by using the MAINTAINED option of the INDEX
command). There are exceptions, however; for instance, a
large table that is only updated overnight and is queried
heavily during the day is a good candidate for non-
maintained secondary indexes.
Index Benchmarking Tips
* Building an index
As we will see below, Modify/Restructure can create a
primary index, but does too much additional work to give
you an accurate measure of indexing time. To see how
quickly Paradox builds an index, use PAL's INDEX command,
which builds a secondary index on a specified field.
* Using an index
When you want to measure query performance using an index,
on the other hand, it is important that you do not
inadvertently include the time taken to rebuild the index.
You will want to use maintained indexes here.
PRODUCT : Paradox NUMBER : 527
VERSION : 3.0
OS : DOS
DATE : August 12, 1992 PAGE : 6/12
TITLE : Performance Notes for Paradox 3.0 Reviewers
* Changing a Table's Structure
Another area in which Paradox provides more support than
is evident at first glance is Modify/Restructure. From
the user's point of view, Restructure allows changing a
table's primary key, adding, deleting, or reordering
fields, or changing field names and lengths. In the
course of making these changes, Restructure does three
other important kinds of work behind the scenes:
o Unless requested not to, Restructure builds a
PROBLEMS table containing any records in which data
would be lost or truncated as a result of changes
to field types or lengths. Like other temporary
tables, PROBLEMS is presented to the user in sorted
order.
o It also removes references to deleted fields from
forms, reports, image settings, and validity
checks. Users have as much flexibility as possible
to change existing tables without losing the forms
and reports they have defined for them, yet are
guaranteed that these forms and reports are
consistent with the new table structure. [expand:
users have object-oriented view -- field either
exists or it doesn't -- and this gives flexibility,
integrity, and simplicity]
o Finally, Restructure always rebuilds a table
completely: it brings records back into physical
sequence, consolidates partly-full blocks of data,
returns the space freed by this rebuilding to DOS,
and rebuilds primary and secondary indexes.
Clearly it is not appropriate to compare Paradox's
time to change a field's type, for instance, to
another product's time for the same change, unless
the other product also provides the same
integrity-checking and table-rebuilding
capabilities.
PRODUCT : Paradox NUMBER : 527
VERSION : 3.0
OS : DOS
DATE : August 12, 1992 PAGE : 7/12
TITLE : Performance Notes for Paradox 3.0 Reviewers
Restructure Benchmarking Tips
* Changing table structure
You cannot get a time for a simple table change (delete a
field, change a field's data type, add a primary index,
etc.) that does not include the additional work discussed
above. Since these operations are not done frequently
once a table contains data and is being used regularly,
Paradox's design favors thoroughness and data integrity
over speedy execution, in this case.
* Compacting a table
You can force Paradox to rebuild a table as described
above by entering the Restructure subsystem and pressing
Do_It! without making any changes. If the table contains
many deleted records, this will compact it and, thus,
speed up operations that use it. Many Paradox users
regularly restructure their tables for just this reason,
and it may be appropriate to do so when benchmarking as
well.
* Building an index
As we have seen, the PAL command INDEX, which builds or
rebuilds a secondary index on the field you select, gives
the best measure of indexing time.
* Renaming fields in ANSWER
Use the AS operator in a query to rename fields in the
ANSWER table. This is especially useful for CALC queries
or when two tables in the query have fields with the same
name.
* Import/Export and Reporting
Sometimes Paradox provides several ways to perform the
same general operation, each with its own options and
advantages. Which alternative you choose will depend on
your specific circumstances and objectives. When optimum
performance is the objective, it is especially important
PRODUCT : Paradox NUMBER : 527
VERSION : 3.0
OS : DOS
DATE : August 12, 1992 PAGE : 8/12
TITLE : Performance Notes for Paradox 3.0 Reviewers
to select the alternative that best satisfies your needs.
We will look at two examples, one that involves inputting
data into Paradox, and one that involves report output.
Paradox imports data in more formats than most other
database products, and can in every case determine the
structure of the target table without the user's
involvement. When importing data in a well-defined
format, such as DIF, this is straightforward. For other
formats, such as 1-2-3 spreadsheets and comma-delimited
ASCII files, Paradox must pass through the file to be
imported twice, once to select data types and once to
import the data. This is slow, but gives the best
possible support for interactive use. In an application,
if the format of the incoming data is known and the data
is clean, you can instead use
Ascii/Import/AppendDelimited. In this case, you provide
the target table, and Paradox only makes one pass through
the data, building a PROBLEMS table if any input records
do not have the necessary structure.
Users often want to borrow reports from an existing table
for a newly-created table, most often to associate an
already-defined report with a new ANSWER table. In
Paradox 2.0, Copy/JustFamily is usually the best tool to
associate forms or reports with a table; to empty the
target table and add records into it is slower. In
Paradox 3.0, an individual form or report may be copied
from one table to another.
Import/Export and Report Benchmarking Tips
* Joining tables
In Paradox 3.0, you can use external tables in a report
specification to satisfy most requests for reports that
draw data from several tables. Only the most complex
reports require a query step to assemble the data before
reporting.
PRODUCT : Paradox NUMBER : 527
VERSION : 3.0
OS : DOS
DATE : August 12, 1992 PAGE : 9/12
TITLE : Performance Notes for Paradox 3.0 Reviewers
* Sorting
Grouping in a report implies sorting; there is no need to
sort a table before reporting on it.
* Copying reports to an ANSWER table
In general, copying a single report from one table to
another is the fastest way to use an existing report with
a new ANSWER table.
* Ascii Import
For fast import into an ASCII table, define the target
table ahead of time and use the AppendDelimited option to
fill it.
* PAL
A program in Paradox's programming language, PAL, can be
as simple as a small macro or a few recorded keystrokes.
At the other extreme, PAL provides many features of
contemporary programming languages, such as arrays, DO and
FOR statements, procedures with arguments and private
variables, and many functions for testing system status.
Using these features, developers can write complex and
sophisticated Paradox applications.
It is possible to write a PAL application without using
procedures. However, programs that use procedures, beside
being cleaner and easier to debug, usually run faster.
Once an application is completed, it is generally
desirable to store its procedures in procedure libraries.
Procedures from libraries usually run faster than those in
scripts (as PAL programs are often called), especially in
large applications, because libraries allow procedure
swapping.
PRODUCT : Paradox NUMBER : 527
VERSION : 3.0
OS : DOS
DATE : August 12, 1992 PAGE : 10/12
TITLE : Performance Notes for Paradox 3.0 Reviewers
PAL Benchmarking Tips
* Procedures
Keep PAL procedures fairly small, store them in procedure
libraries and use procedure calls that allow swapping, and
let Paradox manage loading and releasing of procedures.
* Network Topics
Paradox's support for local-area networks is a natural
extension of its single-user feature set. One of its main
objectives is to allow concurrent operation wherever
possible. Many users can read a table -- for the purpose
of viewing it, querying it, reporting from it, or graphing
it -- simultaneously. Although only one user can edit a
table at a time, a second update mode, CoEdit, is provided
to allow several users to change a table's contents
concurrently. In CoEdit, users lock only the records they
are changing; other users are prevented from trying to
change a record as long as it is locked, but may lock and
change any other records. Still, other users may view,
query, or report from the table while it is being
coedited.
Paradox never allows concurrent access that would put the
integrity of the data at risk, however. To prevent such
access, it places locks on resources -- tables, forms,
reports, and other Paradox objects -- that are in use.
Other users may or may not be allowed to share these
resources, depending on what type of lock is in force and
what type of locks they require for the operations they
want to perform.
Here again, we see how unobtrusively Paradox works to
allow you to concentrate on the work to be done and not on
the mechanics of getting it done. While you continue to
work as you would on a single-user installation, Paradox
ensures that needed resources are available and locks them
to prevent inappropriate concurrent use by others. You
never have to inquire if a table or record is locked
before you try to use it, and you are never penalized if
you fail to check what other users are doing before you
PRODUCT : Paradox NUMBER : 527
VERSION : 3.0
OS : DOS
DATE : August 12, 1992 PAGE : 11/12
TITLE : Performance Notes for Paradox 3.0 Reviewers
begin your work. As long as there is no conflict, you do
not need to pay attention to what other users on the
network are doing -- and as soon as there is a conflict,
Paradox tells you. Moreover, Paradox does not just tell
you that a resource is unavailable; it also tells who is
using the resource you need.
You can, however, ask Paradox to keep you posted on
changes other users are making to the tables you are
viewing or using. By adjusting the AutoRefresh interval,
you can have their work reflected on your screen almost
instantly, so you will always know as you begin updating a
record that the values you are looking at are completely
up-to-date. Or you can turn off AutoRefresh. In either
case, Paradox informs you of changes you need to know
about -- for example, if you attempt to lock a record when
your display of it is out-of-date, Paradox will update
your display before it lets you begin entering your
changes.
Network Benchmarking Tips
* Locking
In addition to the implicit locking discussed above, it is
also possible to lock tables explicitly through PAL. In
an application, it is better to lock explicitly and test
that the lock succeeded.
* Using CoEdit
CoEdit in a shared directory that gives maximum
concurrence, but the additional overhead of record-locking
inevitably causes some performance degradation. Edit
gives better performance. CoEdit with exclusive use of a
directory can be faster than Edit, because it can omit
both the record-locking and the transaction log that Edit
maintains.
PRODUCT : Paradox NUMBER : 527
VERSION : 3.0
OS : DOS
DATE : August 12, 1992 PAGE : 12/12
TITLE : Performance Notes for Paradox 3.0 Reviewers
* Improving network performance
Our default time of three seconds for AutoRefresh assumes
a fairly powerful network, and gives up a fraction of its
resources in order to keep all users up-to-date. For a
heavily-loaded network, you may want to set the
AutoRefresh interval higher, to work in forms view instead
of table view, or to use DataEntry instead of CoEdit. Any
of these will decrease network traffic.
DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.